﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DBUtility;
using System.Data;

namespace DAL
{
   public class FastOutStockDal
    {

       //获取快速出库列表
       public DataSet GetFastOutStockList()
       {
           DataSet ds = new DataSet();
           StringBuilder sql = new StringBuilder();

           sql.Append(" select FInterID,SaleBillNo,NeedQty from t_FastOutStock where status ='0'  ");
           
           try
           {
               DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
               ds = DbHelperSQL.Query(sql.ToString());
           }
           catch (Exception ex)
           {
               throw ex;
           }
           return ds;       
       }

       //获取快速出库明细
       public DataSet GetFastOutStockDetail(string FBillNo)
       {
           DataSet ds = new DataSet();
           StringBuilder sql = new StringBuilder();

           sql.Append("  select ROW_NUMBER() over(order by PkgsBarCode) rownum,PkgsBarCode,FInterid,a.Fsku,b.FItemName Fname,b.AttrValue,StickLen,FQty,SaleBillNo,WorkBillNo,PkgsStatus  ");
           sql.Append("  from t_FastOutStockDetail a left join t_ICItemSku b on a.FSku = b.FSku ");
           sql.Append("   where SaleBillNo ='" + FBillNo + "' and a.status ='0'  ");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                ds = DbHelperSQL.Query(sql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;   
       }

       //插入快速出库明细
       public bool InsertFastOutStockDetail(string FInterID, string FBillNo, string WorkBillNo, string PkgsBarCode, string userid, string len)
       {
           StringBuilder sql = new StringBuilder();
           bool result = false;
           int i = 0;

           if (len == null || len == "") //定尺
           {
               sql.Append(" insert into t_FastOutStockDetail(FIDMAIN,SaleBillNo,WorkBillNo,Fdate,Fry,PkgsBarCode,FSku,StickLen,Stick,FQty,HelperCode,PkgsStatus,Status)");
               sql.Append(" select  '" + FInterID + "' FIDMAIN, '" + FBillNo + "' SaleBillNo,'" + WorkBillNo + "' WorkBillNo,CONVERT(varchar(100), GETDATE(), 120) , '" + userid + "'  Fry,PkgsBarCode");
               sql.Append(",ItemSku,StickLen,Stick,FQty,HelperCode,'0' PkgsStatus,'0' Status from stk_PackagePlc");
               sql.Append(" where PkgsBarCode ='" + PkgsBarCode + "'");
           }
           else //不定尺
           {
               sql.Append(" insert into t_FastOutStockDetail(FIDMAIN,SaleBillNo,WorkBillNo,Fdate,Fry,PkgsBarCode,FSku,StickLen,Stick,FQty,HelperCode,PkgsStatus,Status,len)");
               sql.Append(" select   '" + FInterID + "' FIDMAIN, '" + FBillNo + "' SaleBillNo,'" + WorkBillNo + "' WorkBillNo,CONVERT(varchar(100), GETDATE(), 120) , '" + userid + "'  Fry,PkgsBarCode");
               sql.Append(",ItemSku,StickLen,Stick,FQty,HelperCode,'0' PkgsStatus,'0' Status ,'" + len + "' len from stk_PackagePlc");
               sql.Append(" where PkgsBarCode ='" + PkgsBarCode + "'");
           }

           try
           {
               DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
             
               i = DbHelperSQL.ExecuteSql(sql.ToString());
               if (i > 0)
               {
                   result = true;
               }
           }
           catch (Exception ex)
           {
               throw ex;
           }
           return result;
       }

       //更新快速出库的需求数量
       public bool UpdateFastOutStock(string FBillNo, decimal NeedQty,string FInterID)
       {
           StringBuilder sql = new StringBuilder();
           bool lb_return = false;
           bool result = false;
           int i = 0;

           result = GetFastOutStockFBillNo(FBillNo);
           if (result == true)
           {
               sql.Append("update t_FastOutStock set NeedQty = '" + NeedQty + "' where SaleBillNo ='" + FBillNo + "'  and status='0' and FInterID ='" + FInterID + "'");
           }
           else
           {
               sql.Append("insert into t_FastOutStock(SaleBillNo,NeedQty,Status) values ('" + FBillNo + "','" + NeedQty + "','0')");
           }

           try
           {
               i = DbHelperSQL.ExecuteSql(sql.ToString());
               if (i > 0)
               {
                   lb_return = true;
               }
           }
           catch (Exception ex)
           {
               throw ex;
           }
           return lb_return;
       }

       //判断已扫的但未出库的数量
       public decimal GetFastOutStockScanQty(string FBillNo)
       {
           decimal Qty = 0;
           StringBuilder sql = new StringBuilder();
           DataSet dt = new DataSet();

           sql.Append(" select  isnull(SUM(FQty),0) Qty from t_FastOutStockDetail where SaleBillNo ='" + FBillNo + "' and status ='0' ");

           try
           {
               DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
               dt = DbHelperSQL.Query(sql.ToString());
               if (dt != null)
               {
                   foreach (DataRow dr in dt.Tables[0].Rows)
                   {
                       Qty = decimal.Parse(dr["Qty"].ToString());
                   }
               }
           }
           catch (Exception ex)
           {
               throw ex;
           }
           return Qty;
       }

       //判断销售订单号是否存在
       public bool GetFastOutStockFBillNo(string FBillNo)
       {
           bool lb_return = false;
           StringBuilder sql = new StringBuilder();
           DataSet ds = new DataSet();
           int i = 0;

           sql.Append("select COUNT(0) num  from t_FastOutStock where SaleBillNo ='" + FBillNo + "' and status='0'");
           try
           {
               DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
             
               ds = DbHelperSQL.Query(sql.ToString());
               foreach (DataRow dr in ds.Tables[0].Rows)
               {
                   i = int.Parse(dr["num"].ToString());
                   if (i > 0)
                   {
                       lb_return = true;
                   }
               }
           }
           catch (Exception ex)
           {
               throw ex;
           }
           return lb_return;
       }
       //获取不定尺的根长
       public string GetBDCLen( string PkgsBarCode, out string  len)
         {
         StringBuilder sql = new StringBuilder();
         StringBuilder comT1 = new StringBuilder();
         DataSet dt = new DataSet();
         string state = "0";
         len = "";
         bool result = false;
           bool result1 = false;

         result = JudgestkPkgsBarCode(PkgsBarCode);//判断stk_PackagePlc表中是否有PkgsBarCode
         result1 = JudgeFastPkgsBarCode(PkgsBarCode);

         if (result == true && result1==true)
         { 
         sql.Append("select StickLen  from  ICMO_PLCDTL");
         sql.Append(" where PkgsID in (select PkgsID from stk_PackagePlc where PkgsBarCode ='" + PkgsBarCode + "' and   SUBSTRING(HelperCode,1,1) ='B')   order by StickLen  ");

         try { 
                 dt = DbHelperSQL.Query(sql.ToString());
                 if(dt!=null)
                 {
                     foreach(DataRow dr in dt.Tables[0].Rows)
                     {
                         len = len + dr["StickLen"].ToString() + ',';                
                     }
                 }
             }
           catch(Exception ex)
           {
             throw ex;
           }
         }
         else if (result == false)
         {
             state = "1";//stk_PackagePlc中没有包装码，该包装码不存在或已被汇报
         }
         else if (result1 == false)
         {
             state = "2";//t_FastOutStockDetail中已经存在包装码
         }
         return state;
            }

       //判断stk_PackagePlc表中是否有PkgsBarCode
       public bool JudgeFastPkgsBarCode(string PkgsBarCode)
       {
           StringBuilder comT1 = new StringBuilder();
           DataSet dt = new DataSet();        
           bool result = false;

           comT1.Append("select count(0) num from t_FastOutStockDetail where PkgsBarCode ='" + PkgsBarCode + "'");
           try
           {
               dt = DbHelperSQL.Query(comT1.ToString());
               foreach (DataRow dr in dt.Tables[0].Rows)
               {
                   int num = int.Parse(dr["num"].ToString());
                   if (num == 0)
                   {
                       result = true;
                   }
               }
           }
           catch (Exception ex)
           {
               throw ex;
           }
           return result;
       }

       //判断stk_PackagePlc表中是否有PkgsBarCode
       public bool JudgestkPkgsBarCode(string PkgsBarCode)
       {
           StringBuilder comT1 = new StringBuilder();
           DataSet dt = new DataSet();
           string str = "";
           bool result = false;

           comT1.Append("select count(0) num from stk_PackagePlc where PkgsBarCode ='" + PkgsBarCode + "'");
           try
           {
               dt = DbHelperSQL.Query(comT1.ToString());
               foreach (DataRow dr in dt.Tables[0].Rows)
               {
                   int num = int.Parse(dr["num"].ToString());
                   if (num > 0)
                   {
                       result = true;
                   }
               }
           }
           catch (Exception ex)
           {
               throw ex;
           }
           return result;
       }

       //判断条码是否入库
       public bool JudgeRK( string FIDMAIN)
       {
           StringBuilder sql = new StringBuilder();
           DataTable dt = new DataTable();
           bool result = false;
           int num = 0;

           sql.Append(" select COUNT(0) num from (");
           sql.Append(" select COUNT(0) num1 from stk_Package where ");
           sql.Append(" PkgsBarCode in (select PkgsBarCode from t_FastOutStockDetail where FIDMAIN ='" + FIDMAIN + "')");
           sql.Append("  and PkgsStatus=0");
           sql.Append("  ) a  ,(select COUNT(0) num2  from t_FastOutStockDetail where FIDMAIN ='" + FIDMAIN + "') b where a.num1 = b.num2");
                     
           try
           {
               DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
               
               dt = DbHelperSQL.QueryTable(sql.ToString());
               if (dt != null && dt.Rows.Count > 0)
               {
                   foreach (DataRow dr in dt.Rows)
                   {
                       num = int.Parse(dr["num"].ToString());
                       if (num > 0)
                       {
                           result = true;
                       }
                   }
               }
           }
           catch (Exception ex)
           {
               throw ex;
           }
           return result;
       }

       //完成快速出库单
       public bool FinishFastOutStock(string FBillNo, string userid, string FIDMAIN, out string l_txt)
       {
         
           List<string> sql = new List<string>();
           DataTable dt = new DataTable();
           int i = 0;
           bool lb_return = false;
           l_txt = "快速出库完成失败！";
           bool resultrk = false;
           resultrk=JudgeRK(FIDMAIN);
           if (resultrk == false)
           {
               l_txt = "快速出库单：" + FBillNo + "中的条码未汇报且未入库不能完成！";
               return false;
           }

           bool result = GetBDC(FBillNo); //将不定尺的根明细按由小到大的顺序排序
          // string PkCode ="";
           string time = DateTime.Now.ToString("yyyyMMddHHmmss");
           time = time.Substring(2, time.Length - 2);
          
          // 1对锁库的件和米在表t_SEOrderDetail 中加入PkCode
               //  2根据t_FastOutStockDetail内容在库存表中 中加入PkCode

           //if (result == true) 
           //{
               dt = GetLockList(FBillNo);
               if(dt!=null && dt.Rows.Count >0)
               {
                   foreach (DataRow dr in dt.Rows)
                   {                       
                       string PkCode = time + dr["FDetailID"].ToString();
                       string SaleBillNo = dr["SaleBillNo"].ToString();
                       string FInterID =dr["FInterID"].ToString();
                       string FEntryID = dr["FEntryID"].ToString();
                       string FDetailID = dr["FDetailID"].ToString();
                       string TOTAL = dr["TOTAL"].ToString();
                       string Fsku = dr["Fsku"].ToString();
                       string StickLen = dr["StickLen"].ToString();
                       string stick = dr["stick"].ToString();
                       StringBuilder comT1 = new StringBuilder();
                       StringBuilder comT2 = new StringBuilder();
                       comT1.Append("update t_SEOrderDetail set PkCode ='"+PkCode+"' where FInterID ='"+FInterID+"' and FEntryID ='"+FEntryID+"' and FDetailID ='"+FDetailID+"' ");
                       sql.Add(comT1.ToString());
                       if (FDetailID == "1" || FDetailID == "2" || FDetailID == "3" || FDetailID == "4" || FDetailID == "5")
                       {
                           comT2.Append("update t_FastOutStockDetail set PkCode='" + PkCode + "',status ='1'  where SaleBillNo ='" + SaleBillNo + "' and FSku = '" + Fsku + "' and StickLen='" + StickLen + "' and Stick ='" + stick + "'");
                       }
                       if (FDetailID == "6" || FDetailID == "7" || FDetailID == "8" || FDetailID == "9" || FDetailID == "10")
                       {
                           comT2.Append("update t_FastOutStockDetail set PkCode='" + PkCode + "',status ='1'  where SaleBillNo ='" + SaleBillNo + "' and FSku = '" + Fsku + "' and Len='" + StickLen + "' and Stick ='" + stick + "'");
                       }
                       sql.Add(comT2.ToString());
                   }
                 
                   try
                   {
                       lb_return = true;
                       i = DbHelperSQL.ExecuteSqlTran(sql);
                       if (i > 0)
                       {
                           UpdatePackage(FBillNo, userid, FIDMAIN);//向库存表更新快速出库的条码
                        
                       }
                   }
                   catch (Exception ex)
                   {
                       throw ex;
                   }
               }
           //}
           return lb_return;
       }

       //向库存表更新快速出库的条码
       public bool UpdatePackage(string SaleBillNo, string userid, string FIDMAIN)
       {
           bool result = false;
           List<string> sql = new List<string>();
           DataTable dt = new DataTable();
           string Ntime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
           decimal FQty = 0;
           StringBuilder comT2 = new StringBuilder();
           comT2.Append("select PkCode,SaleBillNo,FSku , StickLen, Stick,FQty ,PkgsBarCode from t_FastOutStockDetail where SaleBillNo ='" + SaleBillNo + "' and FIDMAIN ='"+FIDMAIN+"' ");
           dt = QueryTable(comT2.ToString());
            if (dt != null)
            {
                foreach (DataRow dr in dt.Rows)
                {
                  
                    string PkgsBarCode = dr["PkgsBarCode"].ToString();
                    string PkCode = dr["PkCode"].ToString();
                    string FSku = dr["FSku"].ToString();
                    string StickLen = dr["StickLen"].ToString();
                    string Stick = dr["Stick"].ToString();
                    FQty = FQty +decimal.Parse(dr["FQty"].ToString());
                    StringBuilder comT1 = new StringBuilder();
                    comT1.Append("update stk_Package set HoldBillNo='" + SaleBillNo + "',Holder=" + userid + ",HoldTime='" + Ntime + "', PkCode ='" + PkCode + "',PkgsStatus='1' where PkgsBarCode='" + PkgsBarCode + "' and  ItemSku ='" + FSku + "' and StickLen ='" + StickLen + "' and PkgsStatus='0' ");
                    sql.Add(comT1.ToString());                   
                }
                StringBuilder comT3 = new StringBuilder();
                comT3.Append("update t_FastOutStock set  Status='1',Actual='" + FQty + "'  where SaleBillNo ='" + SaleBillNo + "' and Status='0' and FInterID ='" + FIDMAIN + "'  ");
                sql.Add(comT3.ToString());
                try
                {                   
                    int i = DbHelperSQL.ExecuteSqlTran(sql);
                    if (i > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return result;
       }
     

       //获取要锁库的列表
       public DataTable GetLockList(string FBillNo)
       {
           StringBuilder comT1 = new StringBuilder();
           DataTable dt = new DataTable();

           comT1.Append("  select  m.FInterID ,m.FEntryID,m.FDetailID ,n.* from (");
           comT1.Append("	 select a.FBillNo,a.FInterID ,b.FEntryID,c.FDetailID,b.FSku,c.XRootLenth,c.XRootPerPiece from (");
           comT1.Append("		select FInterID,FBillNo  from t_SEOrder  where FBillNo ='" + FBillNo + "' ");
           comT1.Append("	  ) a ");
           comT1.Append("	  left join t_SEOrderEntry b on a.FInterID = b.FInterID");
           comT1.Append("	  left join t_SEOrderDetail c on a.FInterID = c.FInterID  and b.FEntryID = c.FEntryID");
           comT1.Append("	  where c.FDetailID  in (1,2,3,4,5) and c.XRootLenth !=0 ) m ");
           comT1.Append(" left join ( ");
           comT1.Append("	 select SaleBillNo,Fsku,cast(StickLen as varchar) StickLen,stick,SUM(FQTY) TOTAL,COUNT(0) NUM from t_FastOutStockDetail ");
           comT1.Append("	 where  substring(HelperCode,1,1) ='A'");
           comT1.Append("	 GROUP BY SaleBillNo,Fsku,StickLen,stick");
           comT1.Append(" ) n on m.FBillNo = n.SaleBillNo and m.FSku = n.Fsku  and m.XRootLenth= n.StickLen and m.XRootPerPiece = n.stick");
           comT1.Append("   where n.SaleBillNo is not null");

           // 不定尺
           comT1.Append("  union all   ");
           comT1.Append("	 select  m.FInterID ,m.FEntryID,m.FDetailID ,n.* from (");
           comT1.Append("	 select a.FBillNo,a.FInterID ,b.FEntryID,c.FDetailID,b.FSku,c.XRootLenth,c.XPieceNum,c.XRootZLenth,XRootPerPiece from (");
           comT1.Append("		select FInterID,FBillNo  from t_SEOrder  where FBillNo ='" + FBillNo + "' ");
           comT1.Append("	  ) a ");
           comT1.Append("	  left join t_SEOrderEntry b on a.FInterID = b.FInterID");
           comT1.Append("	  left join t_SEOrderDetail c on a.FInterID = c.FInterID  and b.FEntryID = c.FEntryID");
           comT1.Append("	  where c.FDetailID  in (6,7,8,9,10) and c.XRootLenth !=0 ");
           comT1.Append("	) m ");
           comT1.Append(" left join ( ");
           comT1.Append("	 select SaleBillNo,Fsku,len,stick,SUM(FQTY) TOTAL,COUNT(0) NUM from t_FastOutStockDetail ");
           comT1.Append("	 where  substring(HelperCode,1,1) ='B' and SaleBillNo ='" + FBillNo + "' ");
           comT1.Append("	 GROUP BY SaleBillNo,Fsku,stick,len");
           comT1.Append(" ) n on m.FBillNo = n.SaleBillNo and m.FSku = n.Fsku  and m.XRootZLenth= n.len ");
           comT1.Append("   where n.SaleBillNo is not null");
           try
           {
               DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
               dt = DbHelperSQL.QueryTable(comT1.ToString());
              
           }
           catch (Exception ex)
           {
               throw ex;
           }
           return dt;
       }

       public bool GetBDC(string FBillNo)
       {
           StringBuilder ComT1 = new StringBuilder();
           
           List<string> sql = new List<string>();
           DataSet dt = new DataSet();
           bool lb_return = false;
           
 	         ComT1.Append(" select  a.FBillNo,a.FInterID,c.FEntryID,c.FDetailID,b.FSku,c.XRootLenth,XRootPerPiece,XTotalPieceLenth,c.XPieceNum,c.XRootZLenth from (");
             ComT1.Append(" select FInterID,FBillNo  from t_SEOrder where FBillNo ='" + FBillNo + "' ");
	         ComT1.Append(" ) a ");
	         ComT1.Append(" left join t_SEOrderEntry b on a.FInterID = b.FInterID");
	         ComT1.Append(" left join t_SEOrderDetail c on a.FInterID = c.FInterID  and b.FEntryID = c.FEntryID");
	         ComT1.Append(" where c.FDetailID  in (6,7,8,9,10) and c.XRootLenth !=0 ");

           try{
               DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
           
               dt = DbHelperSQL.Query(ComT1.ToString());
               if(dt!=null)
               {
                   foreach(DataRow dr in dt.Tables[0].Rows)
                   {                     
                      string XRootZLenth = dr["XRootZLenth"].ToString();
                      string FInterID = dr["FInterID"].ToString().Trim();
                      string FDetailID = dr["FDetailID"].ToString().Trim();
                      string FEntryID = dr["FEntryID"].ToString().Trim();
                      string[]  XRootArr = XRootZLenth.Split(',');
                      List<decimal> XRootArrDList = new List<decimal>();
                      decimal[] XRootArrDS;                  
                       string XRootZLenthnew ="";

                       for(int i = 0;i<XRootArr.Length;i++)
                       {
                          decimal XRootArrD  = decimal.Parse(XRootArr[i]) ; 
                          XRootArrDList.Add(XRootArrD);
                       }                    
   
                      XRootArrDS = XRootArrDList.ToArray();
                      Array.Sort(XRootArrDS);
                  
                      for (int i = 0; i < XRootArrDS.Length; i++)
                      {
                          string XRootZLenthStr = XRootArrDS[i].ToString("f2");
                          XRootZLenthnew = XRootZLenthnew + XRootZLenthStr+',';
                      }
                      XRootZLenthnew = XRootZLenthnew.Substring(0, XRootZLenthnew.Length - 1);
                      StringBuilder ComT2 = new StringBuilder();
                     ComT2.Append(" update t_SEOrderDetail set XRootZLenth ='" + XRootZLenthnew + "'  where FInterID='" + FInterID + "' and FEntryID='" + FEntryID + "' and FDetailID = '" + FDetailID + "'");
                    
                       sql.Add(ComT2.ToString());
                   }
                   if (ExecuteSql(sql))
                   {
                       lb_return = true;
                   } 
               }
           }
           catch(Exception ex)
           {
          throw ex;
           }
           return lb_return;
       }

     
        public bool ExecuteSql(List<string> list) 
        {
            bool result = false;
            try
            {
                int i = 0;
                i = DbHelperSQL.ExecuteSqlTran(list);
                if (i > 0)
                {
                    result = true;
                }
                else
                {
                    result = false;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public DataTable QueryTable(string sql)
        {          
            DataTable dt = new DataTable();
            List<string> list = new List<string>();
            list.Add(sql);

            try
            {               
                dt = DbHelperSQL.QueryTable(sql);
                
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

 }
    }

